package com.ethereal.servlet;

import com.ethereal.servlet.Dao.DatabaseUtil;
import com.ethereal.servlet.Entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataBase {
    public static void main(String[] args) {
        try {
            Connection conn = DatabaseUtil.getConnection();
            if (conn != null && !conn.isClosed()){
                //增
                //add(conn);
                //删
                //delete(conn);
                //改
                //update(conn);
                //查
                list(conn);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    private static void add(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO user (user_name, password) values (?, ?)");
        pstmt.setString(1, "li");
        pstmt.setString(2, "123456");
        int rows = pstmt.executeUpdate();
        if (rows == 1){
            System.out.println("添加成功！");
        }else {
            System.out.println("添加失败！");
        }
    }
    private static void delete(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("DELETE FROM user WHERE user_name = ? ");
        pstmt.setString(1,"li");
        int rows = pstmt.executeUpdate();
        if (rows == 1){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }
    }
    private static void update(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("UPDATE user SET user_name = ?, password = ? WHERE user_name = ?");
        pstmt.setString(1,"lipeng");
        pstmt.setString(2,"1234");
        pstmt.setString(3,"li");
        int rows = pstmt.executeUpdate();
        if (rows == 1){
            System.out.println("修改成功！");
        }else {
            System.out.println("修改失败！");
        }
    }
    private static void list(Connection conn) throws SQLException {
        List<User> userList = new ArrayList<>();
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user");
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()){
            User user = new User();
            user.setUserName(rs.getString("user_name"));
            user.setPassword(rs.getString("password"));
            userList.add(user);
        }
        for (User user : userList){
            System.out.println(user.getUserName());
            System.out.println(user.getPassword());
        }
    }
}
